package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.HosyuShubetsuBean;
import jp.bsws.bean.KeyValueBean;
import jp.bsws.common.BSWSConstants;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.HosyuShubetsuModel;
import jp.bsws.model.TenpoRelationModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class HosyuShubetsuDao {
	private static HosyuShubetsuDao hosyuShubetsuDao = new HosyuShubetsuDao();

	/** ログ */
	private Log log = LogFactory.getLog(HosyuShubetsuDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static HosyuShubetsuDao getInstance(){
		return hosyuShubetsuDao;
	}

	private String SQL_SELECT_HOSYU_SHUBETSU = " SELECT * FROM M_HOSYU_SHUBETSU WHERE GROUP_COD = ? ";

	/**
	 * 報酬種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<HosyuShubetsuModel> getHosyuShubetsuJoho(Connection con, Integer pkSeq, String pkGroupCod, String pkTenpoCod) throws Exception{

		List<HosyuShubetsuModel> resultList = new ArrayList<HosyuShubetsuModel>();

		//検索条件の作成
		String whereSeq = "";
		if( pkSeq != null ){
			whereSeq =  " AND SEQ = ? ";
		}

		String whereTenpo = "";
		//店舗で抽出
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			TenpoRelationDao tenpoRelationDao = TenpoRelationDao.getInstance();
			List<TenpoRelationModel> tmpList = tenpoRelationDao.getTenpoRelationInfo(con, pkGroupCod, pkTenpoCod, String.valueOf(BSWSConstants.KBN_HOSYU_SHUBETSU));
			whereTenpo = tenpoRelationDao.makeSeqList(tmpList);
		}

		String orderBy = " ORDER BY HOSYU_SHUBETSU_HYOJI_JUN";
		PreparedStatement stmt = con.prepareStatement(SQL_SELECT_HOSYU_SHUBETSU + whereSeq + whereTenpo + orderBy);
		stmt.setString(1, pkGroupCod);
		if( pkSeq != null ){
			stmt.setInt(2, pkSeq);
		}

		ResultSet rs = stmt.executeQuery();

		HosyuShubetsuModel hosyuShubetsuModel = null;
		while(rs.next()){
			hosyuShubetsuModel = new HosyuShubetsuModel();
			hosyuShubetsuModel.setSeq( rs.getInt("SEQ"));
			hosyuShubetsuModel.setStrGroupCod(rs.getString("GROUP_COD"));
			hosyuShubetsuModel.setStrHosyuShubetsuKnj(rs.getString("HOSYU_SHUBETSU_KNJ"));
			hosyuShubetsuModel.setStrHosyuShubetsuRyakuKnj(rs.getString("HOSYU_SHUBETSU_RYAKU_KNJ"));
			hosyuShubetsuModel.setIntHosyuShubetsuHyojiJun(rs.getInt("HOSYU_SHUBETSU_HYOJI_JUN"));
			hosyuShubetsuModel.setStrHosyuShubetsuHyojiFlg(rs.getString("HOSYU_SHUBETSU_HYOJI_FLG"));
			hosyuShubetsuModel.setStrKeisanMotoCod(rs.getString("KEISAN_MOTO_COD"));
//			hosyuShubetsuModel.setBdWariaiNum(rs.getBigDecimal("WARIAI_NUM"));
			resultList.add(hosyuShubetsuModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_HOSYU_SHUBETSU_INSERT =
		" INSERT INTO " +
		"     M_HOSYU_SHUBETSU ( " +
		"         SEQ " +
		"        ,GROUP_COD " +
		"        ,HOSYU_SHUBETSU_KNJ " +
		"        ,HOSYU_SHUBETSU_RYAKU_KNJ " +
		"        ,HOSYU_SHUBETSU_HYOJI_JUN " +
		"        ,HOSYU_SHUBETSU_HYOJI_FLG " +
		"        ,KEISAN_MOTO_COD " +
		"        ,TOROKU_TMS " +
		"        ,TOROKUSHA_COD " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 報酬種別情報追加
	 */
	public int insertHosyuShubetsu(Connection con, Integer seq, BSWSSession bSWSSession , HosyuShubetsuBean hosyuShubetsuBean) throws Exception{

		BSWSDao bSWSDao = BSWSDao.getInstance();
		//最大値を取得
		Integer maxSeq = bSWSDao.getMaxSeq(con, "M_HOSYU_SHUBETSU");
		if(seq == null){
			seq = maxSeq + 1;
		}

		PreparedStatement stmt = con.prepareStatement(SQL_HOSYU_SHUBETSU_INSERT);
		stmt.setInt(1, seq);
		stmt.setString(2, bSWSSession.getStrGroupCod());
		stmt.setString(3, hosyuShubetsuBean.getStrHosyuShubetsuKnj());
		stmt.setString(4, hosyuShubetsuBean.getStrHosyuShubetsuRyakuKnj());
		stmt.setInt(5, Integer.parseInt(hosyuShubetsuBean.getStrHosyuShubetsuHyojiJun()));
		stmt.setInt(6, Integer.parseInt(hosyuShubetsuBean.getStrHosyuShubetsuHyojiFlg()));
		stmt.setString(7, hosyuShubetsuBean.getStrKeisanMotoCod());
		stmt.setString(8, bSWSSession.getStrJugyoinCod());
		stmt.setString(9, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_HOSYU_SHUBETSU_UPDATE =
		" UPDATE " +
		"     M_HOSYU_SHUBETSU " +
		" SET " +
		"         HOSYU_SHUBETSU_KNJ = ? " +
		"        ,HOSYU_SHUBETSU_RYAKU_KNJ = ? " +
		"        ,HOSYU_SHUBETSU_HYOJI_JUN = ? " +
		"        ,HOSYU_SHUBETSU_HYOJI_FLG = ? " +
		"        ,KEISAN_MOTO_COD = ? " +
		"        ,KOSHIN_TMS = now() " +
		"        ,KOSHINSHA_COD = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 報酬種別情報更新
	 */
	public int updateHosyuShubetsu(Connection con,BSWSSession bSWSSession , HosyuShubetsuBean hosyuShubetsuBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_HOSYU_SHUBETSU_UPDATE);

		stmt.setString(1, hosyuShubetsuBean.getStrHosyuShubetsuKnj());
		stmt.setString(2, hosyuShubetsuBean.getStrHosyuShubetsuRyakuKnj());
		stmt.setInt(3, Integer.parseInt(hosyuShubetsuBean.getStrHosyuShubetsuHyojiJun()));
		stmt.setString(4, hosyuShubetsuBean.getStrHosyuShubetsuHyojiFlg());
		stmt.setString(5, hosyuShubetsuBean.getStrKeisanMotoCod());
		stmt.setString(6, bSWSSession.getStrJugyoinCod());
		stmt.setInt(7, hosyuShubetsuBean.getSeq());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	/**
	 * 店舗に登録されている報酬種別情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getHosyuShubetsuList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		List<HosyuShubetsuModel> tmpList = this.getHosyuShubetsuJoho(con, null, pkGroupCod, pkTenpoCod);
		for(HosyuShubetsuModel hosyuShubetsuModel:tmpList){
			KeyValueBean kv = new KeyValueBean();
			kv.setKey(String.valueOf(hosyuShubetsuModel.getSeq()));
			kv.setValue(hosyuShubetsuModel.getStrHosyuShubetsuRyakuKnj());
			resultList.add(kv);
		}

		return resultList;
	}
}